package com.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import junit.framework.TestCase;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.junit.Test;

import ws.auther.svc.com.SvcUserDetailInfoDto;
import ws.auther.svc.com.SysUser;


import com.csair.cbd.cbdinterface.services.dto.CancelDealResultDto;
import com.csair.cbd.cbdinterface.services.dto.DealResultDto;
import com.csair.cbd.ccs.services.web.Exception.WebServiceException;
import com.csair.cbd.ccs.services.web.datatype.MemberInfoDto;
import com.demo.entity.ConfigContent;
import com.demo.entity.BCTestCaseResultEntity;
import com.demo.source.TestFindUserDetailInfoByAccountAndPwd2;
import com.demo.source.TestFindUserDetailInfo;
import com.demo.source.TestModifyEmployeePasswdByEmployeeID;
import com.demo.source.TestSaveCustomersBandRelationship;
import com.demo.source.TestUnLockUserByEmployeeID;
import com.demo.util.Connector;

import dto.auther.svc.csair.com.SvcOpDataDto;

public class SVCAutherTestCases extends TestCase {

	public ConfigContent config;
	// 生成一个可读取的excel文件对象
	// excel记录
	private static int error = 0;

	// 辅助对象
	MemberInfoDto meberInfoDto;
	CancelDealResultDto cancelDealResultDto;
	DealResultDto dealResultDto;

	private static String useCaseId;
	private static long day1;
	private static long day2;
	private static long days;

	public SVCAutherTestCases(String string, ConfigContent configContent)
			throws IOException, RowsExceededException, WriteException {
		// TODO Auto-generated constructor stub
		super(string);
		this.config = configContent;
	}
	
	public void setUp(){
		con=(new Connector()).getSVCConnection();
	}
	
	public void tearDown(){
		try {
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public Connection con;
	
	

	/**
	 * 
	 * @throws BiffException
	 * @throws IOException
	 * @throws RowsExceededException
	 * @throws WriteException
	 */
	@Test
	public void findUserDetailInfoByAccountAndPwd2() throws BiffException,
			IOException, RowsExceededException, WriteException {
		// 读取EXCEL
		File xls = new File(config.getInPath());
		jxl.Workbook book = Workbook.getWorkbook(xls.getAbsoluteFile());
		Sheet pay = book.getSheet("SVC权限控制接口数据池");

		//
		String[] payStr = new String[pay.getColumns()];
		getName(payStr, pay, 16);// 输入参数字段名

		int line = 17;

		// useCaseId = pay.getCell(0, line).getContents().toString();//用例编号
		String testOrNot = "是"; // = pay.getCell(1,
								// line).getContents().toString();//结果是否输出
		// String print = pay.getCell(2, line).getContents().toString();//结果是否输出
		useCaseId = getItem(pay, "数据编号", payStr, line);
		String mark = "test";

		List<BCTestCaseResultEntity> testCaseResultList = new ArrayList<BCTestCaseResultEntity>();
		BCTestCaseResultEntity testCaseResult;
		List<String> totalResult = new ArrayList<String>();
		while (!useCaseId.equals("") && useCaseId != null) {
			if (!mark.equals(useCaseId)) {
				testOrNot = pay.getCell(1, line).getContents().toString();
				if (mark != "test") {
					if (error == 0) {
						totalResult.add("OK");
					} else {
						totalResult.add("NG");
					}
				}
				error = 0;
				mark = useCaseId;
			}
			if (testOrNot.equals("是")) {
				
				String employeeID = getItem(pay, "用户账号", payStr, line);
				String password = getItem(pay, "密码", payStr, line);
				String sysCode = getItem(pay, "系统代码", payStr, line);
				String loginIP="127.0.0.1";
				
				//修改sql
				if(useCaseId.equals("ST008")){
					String sql="update SVC_USER_FAIL_LOGIN set FAIL_LOGIN_TIMES=5 " +
							", FAIL_LOGIN_DATE_LAST=?,FAIL_LOGIN_DATE_FIRST=?" +
							"where UID=? and LOGIN_SYS=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()-1000*60*60*2));
						ps.setDate(2, new Date(System.currentTimeMillis()-1000*60*60*3));
						ps.setString(3, employeeID);
						ps.setString(4, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST009")){
					String sql="update SVC_USER_FAIL_LOGIN set FAIL_LOGIN_TIMES=4 " +
							", FAIL_LOGIN_DATE_LAST=?,FAIL_LOGIN_DATE_FIRST=?" +
							"where UID=? and LOGIN_SYS=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()-1000*60*60*3));
						ps.setDate(2, new Date(System.currentTimeMillis()-1000*60*60*5));
						ps.setString(3, employeeID);
						ps.setString(4, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST010")){
					String sql="update SVC_USER_FAIL_LOGIN set FAIL_LOGIN_TIMES=5 " +
							", FAIL_LOGIN_DATE_LAST=?,FAIL_LOGIN_DATE_FIRST=?" +
							"where UID=? and LOGIN_SYS=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()-1000*60*60*24-1000*300));
						ps.setDate(2, new Date(System.currentTimeMillis()-1000*60*60*3));
						ps.setString(3, employeeID);
						ps.setString(4, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST011")){
					String sql="update SVC_USER_ACCOUNT set EXPIRE_DATE=? "+
							"where EMPLOYEE_ID=? and APP_SYS_CODE=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()+1000*60*60*24));
						ps.setString(2, employeeID);
						ps.setString(3, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST012")){
					String sql="update SVC_USER_ACCOUNT set EXPIRE_DATE=? "+
							"where EMPLOYEE_ID=? and APP_SYS_CODE=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()+1000*300));
						ps.setString(2, employeeID);
						ps.setString(3, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST013")){
					String sql="update SVC_USER_ACCOUNT set EXPIRE_DATE=? "+
							"where EMPLOYEE_ID=? and APP_SYS_CODE=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()-1000*60*60*24));
						ps.setString(2, employeeID);
						ps.setString(3, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST014")){
					String sql="update SVC_USER_ACCOUNT set PWD_OP_DATE=? "+
							"where EMPLOYEE_ID=? and APP_SYS_CODE=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()-1000*60*60*24*119+1000*300));
						ps.setString(2, employeeID);
						ps.setString(3, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST015")){
					String sql="update SVC_USER_ACCOUNT set PWD_OP_DATE=? "+
							"where EMPLOYEE_ID=? and APP_SYS_CODE=?";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()-1000*60*60*24*120+1000*300));
						ps.setString(2, employeeID);
						ps.setString(3, sysCode);					
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}else if(useCaseId.equals("ST016")){
					String sql="update SVC_USER_ACCOUNT set PWD_OP_DATE=? "+
							"where EMPLOYEE_ID=? and APP_SYS_CODE=?";
					try {						
						SimpleDateFormat sdf=new SimpleDateFormat( " yyyy-MM-dd HH:mm:ss ");  //格式化时间
					    Date sDate=(Date) sdf.parse(" 2002-10-8 15:30:22 "); //格式化startDate 开始时间为 Date类型					   
					    java.sql.Date staDate = new java.sql.Date(sDate.getTime()); //开始时间 转换成sqldate类型
					    PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1,staDate);
						ps.setString(2, employeeID);
						ps.setString(3, sysCode);					
						ps.execute();					
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}			
				
				/*
				SvcOpDataDto opDate = new SvcOpDataDto();
				opDate.setChannelId("B2B");
				opDate.setChannelIdPassword("999999");
				opDate.setOpId(getItem(pay, "操作人ID", payStr, line));*/

				try {
					/********************************/
					TestFindUserDetailInfoByAccountAndPwd2 testCheckUserByEmployee = new TestFindUserDetailInfoByAccountAndPwd2(
							config.getEnvironment());
					day1 = System.currentTimeMillis();
					SvcUserDetailInfoDto message = testCheckUserByEmployee.testFindUserDetailInfoByAccountAndPwd2(employeeID, password, sysCode, loginIP);
					day2 = System.currentTimeMillis();
					days = day2 - day1;
					/********************************/

					
					
					if(getItem(pay, "返回值", payStr, line).equals("登录成功")){
						if (checkResult("结果校验", message.getUId(), getItem(pay, "UID", payStr, line))) {					
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(message.getUId());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(message.getUId());
						testCaseResult.setResult("NG");
						error++;
					}
					}else{
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(message.getUId());
						testCaseResult.setResult("NG");
						error++;
					}
					testCaseResultList.add(testCaseResult);

				} catch (WebServiceException e) {

					if (checkResult("结果校验", e.getKey(),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);

				} catch (Exception e) {
					if (checkResult("结果校验", e.toString().substring(100),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(getItem(pay, "返回值", payStr,
								line));
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.toString().substring(100));
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);
				}

				// setCaseStatus();
			}
			useCaseId = getItem(pay, "数据编号", payStr, ++line);
			

		}
		if (mark != "test") {
			if (error == 0) {
				totalResult.add("OK");
			} else {
				totalResult.add("NG");
			}
		}
		error = 0;

		book.close();
		writeDataSourceExcel( 8, 17, testCaseResultList, 17,10, totalResult);

	}

	@Test
	public void unLockUserByEmployeeID() throws BiffException,
			IOException, RowsExceededException, WriteException {
		// 读取EXCEL
		File xls = new File(config.getInPath());
		jxl.Workbook book = Workbook.getWorkbook(xls.getAbsoluteFile());
		Sheet pay = book.getSheet("SVC权限控制接口数据池");

		//
		String[] payStr = new String[pay.getColumns()];
		getName(payStr, pay, 37);// 输入参数字段名

		int line = 38;

		// useCaseId = pay.getCell(0, line).getContents().toString();//用例编号
		String testOrNot = "是"; // = pay.getCell(1,
								// line).getContents().toString();//结果是否输出
		// String print = pay.getCell(2, line).getContents().toString();//结果是否输出
		useCaseId = getItem(pay, "数据编号", payStr, line);
		String mark = "test";

		List<BCTestCaseResultEntity> testCaseResultList = new ArrayList<BCTestCaseResultEntity>();
		BCTestCaseResultEntity testCaseResult;
		List<String> totalResult = new ArrayList<String>();
		while (!useCaseId.equals("") && useCaseId != null) {
			if (!mark.equals(useCaseId.substring(0, 5))) {
				testOrNot = pay.getCell(1, line).getContents().toString();
				if (mark != "test") {
					if (error == 0) {
						totalResult.add("OK");
					} else {
						totalResult.add("NG");
					}
				}
				error = 0;
				mark = useCaseId.substring(0, 5);
			}
			if (testOrNot.equals("是")) {
				
				String employeeID = getItem(pay, "员工号", payStr, line);
				String loginIP="127.0.0.1";
				
				SvcOpDataDto opdataDto=new SvcOpDataDto();
				opdataDto.setChannelID("B2B");
				opdataDto.setChannelIdPassword("999999");
				opdataDto.setOpId("999999");
				opdataDto.setOpIdPassword("999999");
				
				if(useCaseId.equals("ST019")||useCaseId.equals("ST020")){
					String sql="update SVC_USER_FAIL_LOGIN set FAIL_LOGIN_TIMES=5 " +
							", FAIL_LOGIN_DATE_LAST=?" +
							"where UID=? ";
					try {
						PreparedStatement ps=con.prepareStatement(sql);
						ps.setDate(1, new Date(System.currentTimeMillis()));
						ps.setString(2, employeeID);				
						ps.execute();					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				
				/*
				SvcOpDataDto opDate = new SvcOpDataDto();
				opDate.setChannelId("B2B");
				opDate.setChannelIdPassword("999999");
				opDate.setOpId(getItem(pay, "操作人ID", payStr, line));*/

				try {
					/********************************/
					TestUnLockUserByEmployeeID testUnLockUserByEmployeeID = new TestUnLockUserByEmployeeID(
							config.getEnvironment());
					day1 = System.currentTimeMillis();
					testUnLockUserByEmployeeID.testUnLockUserByEmployeeID(employeeID, opdataDto);
					day2 = System.currentTimeMillis();
					days = day2 - day1;
					/********************************/

					
					if (getItem(pay, "返回值", payStr, line).equals("解锁成功")) {
						testCaseResult = new BCTestCaseResultEntity();
						//testCaseResult.setMessage(message);
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						//testCaseResult.setMessage(message);
						testCaseResult.setResult("NG");
						error++;
					}
					testCaseResultList.add(testCaseResult);

				} catch (WebServiceException e) {

					if (checkResult("结果校验", e.getKey(),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);

				} catch (Exception e) {
					if (checkResult("结果校验", e.toString().substring(79),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(getItem(pay, "返回值", payStr,
								line));
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.toString().substring(60));
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);
				}

				// setCaseStatus();
			}
			useCaseId = getItem(pay, "数据编号", payStr, ++line);
			

		}
		if (mark != "test") {
			if (error == 0) {
				totalResult.add("OK");
			} else {
				totalResult.add("NG");
			}
		}
		error = 0;

		book.close();
		writeDataSourceExcel( 5, 38, testCaseResultList, 17,26, totalResult);

	}
	
	
	@Test
	public void modifyEmployeePasswdByEmployeeID() throws BiffException,
			IOException, RowsExceededException, WriteException {
		// 读取EXCEL
		File xls = new File(config.getInPath());
		jxl.Workbook book = Workbook.getWorkbook(xls.getAbsoluteFile());
		Sheet pay = book.getSheet("SVC权限控制接口数据池");

		//
		String[] payStr = new String[pay.getColumns()];
		getName(payStr, pay, 46);// 输入参数字段名

		int line = 47;

		// useCaseId = pay.getCell(0, line).getContents().toString();//用例编号
		String testOrNot = "是"; // = pay.getCell(1,
								// line).getContents().toString();//结果是否输出
		// String print = pay.getCell(2, line).getContents().toString();//结果是否输出
		useCaseId = getItem(pay, "数据编号", payStr, line);
		String mark = "test";

		List<BCTestCaseResultEntity> testCaseResultList = new ArrayList<BCTestCaseResultEntity>();
		BCTestCaseResultEntity testCaseResult;
		List<String> totalResult = new ArrayList<String>();
		while (!useCaseId.equals("") && useCaseId != null) {
			if (!mark.equals(useCaseId)) {
				testOrNot = pay.getCell(1, line).getContents().toString();
				if (mark != "test") {
					if (error == 0) {
						totalResult.add("OK");
					} else {
						totalResult.add("NG");
					}
				}
				error = 0;
				mark = useCaseId;
			}
			if (testOrNot.equals("是")) {
				
				String employeeID = getItem(pay, "员工号", payStr, line);
				String oldPwd = getItem(pay, "旧密码", payStr, line);
				String newPwd = getItem(pay, "新密码", payStr, line);
				
				SvcOpDataDto opdataDto=new SvcOpDataDto();
				opdataDto.setChannelID("B2B");
				opdataDto.setChannelIdPassword("999999");
				opdataDto.setOpId("999999");
				opdataDto.setOpIdPassword("999999");					
				/*
				SvcOpDataDto opDate = new SvcOpDataDto();
				opDate.setChannelId("B2B");
				opDate.setChannelIdPassword("999999");
				opDate.setOpId(getItem(pay, "操作人ID", payStr, line));*/

				try {
					/********************************/
					TestModifyEmployeePasswdByEmployeeID testModifyEmployeePasswdByEmployeeID = new TestModifyEmployeePasswdByEmployeeID(
							config.getEnvironment());
					day1 = System.currentTimeMillis();
					testModifyEmployeePasswdByEmployeeID.testModifyEmployeePasswdByEmployeeID(employeeID, oldPwd, newPwd, opdataDto);
					day2 = System.currentTimeMillis();
					days = day2 - day1;
					/********************************/

					
					if (getItem(pay, "返回值", payStr, line).equals("修改成功")) {
						testCaseResult = new BCTestCaseResultEntity();
						//testCaseResult.setMessage(message);
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						//testCaseResult.setMessage(message);
						testCaseResult.setResult("NG");
						error++;
					}
					testCaseResultList.add(testCaseResult);
					
					//清除数据
					testModifyEmployeePasswdByEmployeeID.testModifyEmployeePasswdByEmployeeID(employeeID, newPwd, oldPwd, opdataDto);			

				} catch (WebServiceException e) {

					if (checkResult("结果校验", e.getKey(),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);

				} catch (Exception e) {
					if (checkResult("结果校验", e.toString().substring(92),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(getItem(pay, "返回值", payStr,
								line));
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.toString().substring(92));
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);
				}

				// setCaseStatus();
			}
			useCaseId = getItem(pay, "数据编号", payStr, ++line);
			

		}
		if (mark != "test") {
			if (error == 0) {
				totalResult.add("OK");
			} else {
				totalResult.add("NG");
			}
		}
		error = 0;

		book.close();
		writeDataSourceExcel( 7, 47, testCaseResultList, 17,30, totalResult);

	}
	
	

	@Test
	public void findUserDetailInfo() throws BiffException,
			IOException, RowsExceededException, WriteException {
		// 读取EXCEL
		File xls = new File(config.getInPath());
		jxl.Workbook book = Workbook.getWorkbook(xls.getAbsoluteFile());
		Sheet pay = book.getSheet("SVC权限控制接口数据池");

		//
		String[] payStr = new String[pay.getColumns()];
		getName(payStr, pay, 69);// 输入参数字段名

		int line = 70;

		// useCaseId = pay.getCell(0, line).getContents().toString();//用例编号
		String testOrNot = "是"; // = pay.getCell(1,
								// line).getContents().toString();//结果是否输出
		// String print = pay.getCell(2, line).getContents().toString();//结果是否输出
		useCaseId = getItem(pay, "数据编号", payStr, line);
		String mark = "test";

		List<BCTestCaseResultEntity> testCaseResultList = new ArrayList<BCTestCaseResultEntity>();
		BCTestCaseResultEntity testCaseResult;
		List<String> totalResult = new ArrayList<String>();
		while (!useCaseId.equals("") && useCaseId != null) {
			if (!mark.equals(useCaseId)) {
				testOrNot = pay.getCell(1, line).getContents().toString();
				if (mark != "test") {
					if (error == 0) {
						totalResult.add("OK");
					} else {
						totalResult.add("NG");
					}
				}
				error = 0;
				mark = useCaseId;
			}
			if (testOrNot.equals("是")) {
				
				String employeeID = getItem(pay, "UID", payStr, line);
				String sysCode = getItem(pay, "系统代码", payStr, line);
				
			
				try {
					/********************************/
					TestFindUserDetailInfo testFindUserDetailInfo = new TestFindUserDetailInfo(
							config.getEnvironment());
					day1 = System.currentTimeMillis();
					SysUser  sysUser=testFindUserDetailInfo.testFindUserDetailInfo(employeeID, sysCode);
					day2 = System.currentTimeMillis();
					days = day2 - day1;
					/********************************/

					
					
					String sql="select * from  SVC_USER_ACCOUNT " +
							"where UID=? and APP_SYS_CODE=?";
					
					try {
						PreparedStatement ps=con.prepareStatement(sql);						
						ps.setString(1, employeeID);	
						ps.setString(2, sysCode);
						ResultSet rs=ps.executeQuery();				
						
						//sysUser.get					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
									
					if (getItem(pay, "返回值", payStr, line).equals("返回权限对象，增加密码有效期日期")) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(sysUser.getUId());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(sysUser.getUId());
						testCaseResult.setResult("NG");
						error++;
					}
					testCaseResultList.add(testCaseResult);
					
					

				} catch (WebServiceException e) {

					if (checkResult("结果校验", e.getKey(),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);

				} catch (Exception e) {
					if (checkResult("结果校验", e.toString().substring(78),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(getItem(pay, "返回值", payStr,
								line));
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.toString().substring(78));
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);
				}

				// setCaseStatus();
			}
			useCaseId = getItem(pay, "数据编号", payStr, ++line);
			

		}
		if (mark != "test") {
			if (error == 0) {
				totalResult.add("OK");
			} else {
				totalResult.add("NG");
			}
		}
		error = 0;

		book.close();
		writeDataSourceExcel( 6, 70, testCaseResultList, 17,48, totalResult);

	}
	
	

	@Test
	public void findUserDetailInfo() throws BiffException,
			IOException, RowsExceededException, WriteException {
		// 读取EXCEL
		File xls = new File(config.getInPath());
		jxl.Workbook book = Workbook.getWorkbook(xls.getAbsoluteFile());
		Sheet pay = book.getSheet("SVC权限控制接口数据池");

		//
		String[] payStr = new String[pay.getColumns()];
		getName(payStr, pay, 69);// 输入参数字段名

		int line = 70;

		// useCaseId = pay.getCell(0, line).getContents().toString();//用例编号
		String testOrNot = "是"; // = pay.getCell(1,
								// line).getContents().toString();//结果是否输出
		// String print = pay.getCell(2, line).getContents().toString();//结果是否输出
		useCaseId = getItem(pay, "数据编号", payStr, line);
		String mark = "test";

		List<BCTestCaseResultEntity> testCaseResultList = new ArrayList<BCTestCaseResultEntity>();
		BCTestCaseResultEntity testCaseResult;
		List<String> totalResult = new ArrayList<String>();
		while (!useCaseId.equals("") && useCaseId != null) {
			if (!mark.equals(useCaseId)) {
				testOrNot = pay.getCell(1, line).getContents().toString();
				if (mark != "test") {
					if (error == 0) {
						totalResult.add("OK");
					} else {
						totalResult.add("NG");
					}
				}
				error = 0;
				mark = useCaseId;
			}
			if (testOrNot.equals("是")) {
				
				String employeeID = getItem(pay, "UID", payStr, line);
				String sysCode = getItem(pay, "系统代码", payStr, line);
				
			
				try {
					/********************************/
					TestFindUserDetailInfo testFindUserDetailInfo = new TestFindUserDetailInfo(
							config.getEnvironment());
					day1 = System.currentTimeMillis();
					SysUser  sysUser=testFindUserDetailInfo.testFindUserDetailInfo(employeeID, sysCode);
					day2 = System.currentTimeMillis();
					days = day2 - day1;
					/********************************/

					
					
					String sql="select * from  SVC_USER_ACCOUNT " +
							"where UID=? and APP_SYS_CODE=?";
					
					try {
						PreparedStatement ps=con.prepareStatement(sql);						
						ps.setString(1, employeeID);	
						ps.setString(2, sysCode);
						ResultSet rs=ps.executeQuery();				
						
						//sysUser.get					
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
									
					if (getItem(pay, "返回值", payStr, line).equals("返回权限对象，增加密码有效期日期")) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(sysUser.getUId());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(sysUser.getUId());
						testCaseResult.setResult("NG");
						error++;
					}
					testCaseResultList.add(testCaseResult);
					
					

				} catch (WebServiceException e) {

					if (checkResult("结果校验", e.getKey(),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.getKey());
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);

				} catch (Exception e) {
					if (checkResult("结果校验", e.toString().substring(78),
							getItem(pay, "返回值", payStr, line))) {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(getItem(pay, "返回值", payStr,
								line));
						testCaseResult.setResult("OK");
					} else {
						testCaseResult = new BCTestCaseResultEntity();
						testCaseResult.setMessage(e.toString().substring(78));
						testCaseResult.setResult("NG");
					}
					testCaseResultList.add(testCaseResult);
				}

				// setCaseStatus();
			}
			useCaseId = getItem(pay, "数据编号", payStr, ++line);
			

		}
		if (mark != "test") {
			if (error == 0) {
				totalResult.add("OK");
			} else {
				totalResult.add("NG");
			}
		}
		error = 0;

		book.close();
		writeDataSourceExcel( 6, 70, testCaseResultList, 17,48, totalResult);

	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	public void writeDataSourceExcel(int startline,
			int startline2, List<BCTestCaseResultEntity> list, int j, int k,
			List<String> totalResult) {
		// TODO Auto-generated method stub

		try {

			/*
			 * rwb = Workbook.getWorkbook(new File(path));
			 * 
			 * // 打开一个文件的副本，并且指定数据写回到原文件 WritableWorkbook wwb =
			 * Workbook.createWorkbook(new File(config.getOutPath()), rwb);//
			 * copy
			 * 
			 * WritableSheet ws = wwb.getSheet("数据池");
			 * 
			 * WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
			 * WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
			 * jxl.format.Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色
			 * WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
			 * wcf.setBackground(jxl.format.Colour.RED);
			 * 
			 * 
			 * 
			 * for(int i=0;i<list.size();i++){ WritableCell wc1 =
			 * ws.getWritableCell(startline, startline2+i); WritableCell wc2 =
			 * ws.getWritableCell(startline+1, startline2+i); CellFormat cf1 =
			 * wc1.getCellFormat();
			 * 
			 * CellFormat cf2 = wc2.getCellFormat();
			 * 
			 * Label label1= new Label(startline,
			 * startline2+i,list.get(i).getMessage()); Label label2= new
			 * Label(startline+1, startline2+i,list.get(i).getResult());
			 * label1.setCellFormat(cf1);
			 * if(list.get(i).getResult().equals("NG")){
			 * label2.setCellFormat(wcf); }else{ label2.setCellFormat(cf2); }
			 * 
			 * 
			 * ws.addCell(label1); ws.addCell(label2); }
			 * 
			 * 
			 * 
			 * 
			 * 
			 * 
			 * // 打开一个文件的副本，并且指定数据写回到原文件
			 * 
			 * WritableSheet ws1 = wwb.getSheet("大客户接口测试用例");
			 * 
			 * for(int i=0;i<totalResult.size();i++){ WritableCell wc1 =
			 * ws1.getWritableCell(j, k+i); Label label3= new Label(j,
			 * k+i,totalResult.get(i)); CellFormat cf1 = wc1.getCellFormat();
			 * label3.setCellFormat(cf1); ws1.addCell(label3); }
			 * 
			 * wwb.write(); wwb.close(); rwb.close();
			 */

			String inPath,outPath;			
			if(!(new File(config.getOutPath()).exists())){
				inPath=config.getInPath();
				outPath=config.getOutPath();
			}else{
				inPath=config.getOutPath();
				outPath=config.getOutPath();
			}
			
			
			HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inPath));	
						
			HSSFSheet sheet = workbook.getSheet("SVC权限控制接口数据池");
			for (int i = 0; i < list.size(); i++) {
				
				if(list.get(i).isSvcBigClientStatResultDtoIsNot()){
					
					HSSFRow row1 = sheet.getRow((short) startline2 + i);
					HSSFCell cell1 = row1.getCell((short) startline);
					cell1.setCellValue(list.get(i).getMessage());
					
					HSSFRow row2 = sheet.getRow((short) startline2 + i);
					HSSFCell cell2 = row2.getCell((short) startline+1);
					cell2.setCellValue(list.get(i).getStaffTotal());
					
					HSSFRow row3 = sheet.getRow((short) startline2 + i);
					HSSFCell cell3 = row3.getCell((short) startline+2);
					cell3.setCellValue(list.get(i).getSeniorNum());
					
					if(!(list.get(i).getEmphasisNum().equals("")||list.get(i).getEmphasisNum().equals(null)))
					{
						HSSFRow row4 = sheet.getRow((short) startline2 + i);
						HSSFCell cell4 = row4.getCell((short) startline+3);
						cell4.setCellValue(list.get(i).getEmphasisNum());
						
					}
					
					
					HSSFRow row5 = sheet.getRow((short) startline2 + i);
					HSSFCell cell5 = row5.getCell((short) startline+4);
					cell5.setCellValue(list.get(i).getResult());					
					
				}else{
				HSSFRow row1 = sheet.getRow((short) startline2 + i);
				HSSFCell cell1 = row1.getCell((short) startline);
				HSSFRow row2 = sheet.getRow((short) startline2 + i);
				HSSFCell cell2 = row2.getCell((short) startline + 1);				
				cell1.setCellValue(list.get(i).getMessage());
				cell2.setCellValue(list.get(i).getResult());
				}
			}

			HSSFSheet sheet1 = workbook.getSheet("SVC权限控制测试用例 ");
			for (int i = 0; i < totalResult.size(); i++) {				
				HSSFRow row1 = sheet1.getRow((short) k + i);
				HSSFCell cell1 = row1.getCell((short) j);
				//System.out.println(cell1.getStringCellValue());
				//cell1.removeCellComment();
				if (cell1 == null) {
					cell1 = row1.createCell((short) j);
					// cell1.setCellStyle(style)
				}
				cell1.setCellValue(totalResult.get(i));
			//	System.out.println(cell1.getStringCellValue());
			}

			FileOutputStream stream = new FileOutputStream(new File(outPath));
			workbook.write(stream);
			stream.close();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	/**
	 * 方法描述 : 取sheet页s中第line行的数据,赋值给字符串str
	 * 
	 * @输入参数 str指定的字符串,s驱动表中的页,line 行
	 * @输出参数
	 * @创建人及日期 : 2012/02/22 by 黄育莲
	 * @最后一次修改 :
	 */
	public void getName(String[] str, Sheet s, int line) {
		for (int i = 0; i < s.getColumns(); i++) {
			str[i] = s.getCell(i, line).getContents().toString();
		}
	}

	public String getItem(Sheet s, String name, String[] str, int lineNum) {
		String re = s.getCell(getColumn(name, str), lineNum).getContents()
				.toString();
		System.out.println(name + ":" + re);
		return re;

	}

	/**
	 * 方法描述 : 获取一字符在数组中的下标, 以此函数来获取指定字段名在excel文件中所在的列数
	 * 
	 * @输入参数 str指定的字符,data数组
	 * @输出参数 int指定字符在数组中的下标
	 * @创建人及日期 : 2012/02/22 by 黄育莲
	 * @最后一次修改 :
	 */
	public int getColumn(String str, String[] data) {
		int column = 0;
		for (int i = 0; i < data.length; i++) {
			if (str.equals(data[i])) {
				column = i;
				break;
			}
		}
		return column;
	}

	/**
	 * 校验接口输出与期望值
	 * 
	 * @param string
	 *            参数名
	 * @param output
	 *            输出
	 * @param expect
	 *            期望值
	 * @throws WriteException
	 * @throws RowsExceededException
	 */
	private boolean checkResult(Object string, Object output, Object expect)
			throws RowsExceededException, WriteException {
		// TODO Auto-generated method stub
		try {
			if (output instanceof String) {
				// if (!(expect.toString().equals("") ||
				// expect.toString().equals(
				// null))) {
				if (!output.toString().contains(expect.toString())) {

					/*
					 * errorSheet.addCell(new Label(0, totalError + 1,
					 * useCaseId)); errorSheet.addCell(new Label(1, totalError +
					 * 1, string .toString())); errorSheet.addCell(new Label(2,
					 * totalError + 1, output .toString()));
					 * errorSheet.addCell(new Label(3, totalError + 1, expect
					 * .toString()));
					 */

					error++;
					return false;

				}
				// }
			} else if (output instanceof Double || output instanceof Integer) {
				if (!(expect.toString().equals("") || expect.toString().equals(
						null))) {
					if (!output.toString().contains(expect.toString())) {
						/*
						 * errorSheet.addCell(new Label(0, totalError + 1,
						 * useCaseId)); errorSheet.addCell(new Label(1,
						 * totalError + 1, string .toString()));
						 * errorSheet.addCell(new Label(2, totalError + 1,
						 * output .toString())); errorSheet.addCell(new Label(3,
						 * totalError + 1, expect .toString()));
						 */
					
						error++;
						return false;

					}
				}
			} else {
				/*
				 * errorSheet.addCell(new Label(0, totalError + 1, useCaseId));
				 * errorSheet.addCell(new Label(1, totalError + 1, string
				 * .toString())); errorSheet.addCell(new Label(2, totalError +
				 * 1, "未能识别该数据类型:" + output.getClass().getName()));
				 * errorSheet.addCell(new Label(3, totalError + 1, expect
				 * .toString()));
				 */
				
				error++;
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}



}
